package com.yuntu.dao.impl;

import com.yuntu.dao.StudentDao;
import com.yuntu.entity.Grade;
import com.yuntu.entity.Student;
import com.yuntu.utils.BaseDao;
import com.yuntu.utils.DateUtil;
import com.yuntu.utils.JDBCUtils;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author MrWang-shuizhishuiheshui
 * @description
 */
public class StudentDaoImpl extends BaseDao implements StudentDao {
    @Override
    public boolean insert(Student student) {
        String sql="insert into student(loginpwd,stuname,sex,gradeid,borndate,identitycard) values(?,?,?,?,?,?)";
        Object[] params={student.getLoginPwd(),
                student.getStuName(),student.getSex(),
                student.getGradeId(), DateUtil.utilDatetoSqlDate(student.getBornDate()),
                student.getIdentityCard()};
        boolean flag = false;
        try {
            flag=executeUpd(sql, params);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeAll(con,ps,null);
        }
        return flag;
    }

    @Override
    public boolean delete(int studentNo) {
        String sql="delete from student where studentno=?";
        Object[] params={studentNo};
        try {
            return executeUpd(sql,params);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeAll(con,ps,null);
        }
        return false;
    }

    @Override
    public boolean update(Student student) {
        String sql="update student set stuname=? where studentno=?";
        Object[] params={student.getStuName(),student.getStudentNo()};
        try {
            return executeUpd(sql,params);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.closeAll(con,ps,null);
        }
        return false;
    }

    private Student rsToStu(ResultSet rs) throws SQLException {
        Student stu=new Student();
        stu.setStudentNo(rs.getInt("studentno"));
        stu.setStuName(rs.getString("stuname"));
        stu.setSex(rs.getString("sex"));
        stu.setBornDate(rs.getDate("borndate"));
        stu.setLoginPwd(rs.getString("loginpwd"));
        stu.setIdentityCard(rs.getString("identitycard"));
        stu.setGradeId(rs.getInt("gradeid"));
        return stu;
    }

    @Override
    public Student selectOneByNo(int studentNo) {
        String sql="select * from student where studentno=?";
        Object[] params={studentNo};
        Student stu=new Student();
        try {
            rs = executeQue(sql, params);
            if (rs.next()){
                stu=rsToStu(rs);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeAll(con,ps,rs);
        }
        return stu;
    }

    @Override
    public List<Student> selectAllByPage(int pageIndex, int pageSize) {
        String sql="select * from student limit ?,?";
        Object[] params={(pageIndex-1)*pageSize,pageSize};
        List<Student> list=new ArrayList<>();
        try {
            rs = executeQue(sql, params);
            while(rs.next()){
                Student stu=rsToStu(rs);
                //将对象放入集合中
                list.add(stu);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeAll(con,ps,rs);
        }
        return list;
    }

    @Override
    public List<Student> selectBothByPage(int pageIndex, int pageSize) {
        String sql="select * from student s " +
                "inner join grade g on s.gradeid=g.id order by studentno limit ?,?";
        Object[] params={(pageIndex-1)*pageSize,pageSize};
        List<Student> list=new ArrayList<>();

        try {
            rs = executeQue(sql, params);
            while(rs.next()){
                Student stu=rsToStu(rs);
                Grade grade=new Grade();
                grade.setId(rs.getInt("id"));
                grade.setGradeName(rs.getString("gradename"));
                stu.setGrade(grade);
                //将对象放入集合中
                list.add(stu);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeAll(con,ps,rs);
        }
        return list;
    }
}
